﻿using DAL.DataProvider;
using DAL.DataTransferObject;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Text;

namespace DAL
{
    public class HangHoaDAO : DataAccess
    {
        public void HangHoa_AddNew(HangHoa hh)
        {
            SqlCommand cmd = new SqlCommand("INSERT INTO HangHoa VALUES(@MaHang,@TenHang,@DonVi,@MaNPP,@SoLuong,@GiaGoc,@SoLuongDongGoi,@GiaDongGoi,@VAT,@TenNSX,@NgaySX,@HanSD,@GhiChu,@XuatXu,@LoaiHang,@KhoChua)", DataAccess.GetConnection());
            cmd.Parameters.Add(new SqlParameter("@MaHang", SqlDbType.Char)).Value = hh.maHang;
            cmd.Parameters.Add(new SqlParameter("@TenHang", SqlDbType.VarChar)).Value = hh.tenHang;
            cmd.Parameters.Add(new SqlParameter("@DonVi", SqlDbType.VarChar)).Value = hh.donVi;
            cmd.Parameters.Add(new SqlParameter("@MaNPP", SqlDbType.Char)).Value = hh.maNPP;
            cmd.Parameters.Add(new SqlParameter("@SoLuong", SqlDbType.Int)).Value = hh.soLuong;
            cmd.Parameters.Add(new SqlParameter("@GiaGoc", SqlDbType.Int)).Value = hh.giaGoc;
            cmd.Parameters.Add(new SqlParameter("@SoLuongDongGoi", SqlDbType.Int)).Value = hh.soLuongDongGoi;
            cmd.Parameters.Add(new SqlParameter("@VAT", SqlDbType.Int)).Value = hh.VAT;
            cmd.Parameters.Add(new SqlParameter("@TenNSX", SqlDbType.VarChar)).Value = hh.tenNSX;
            cmd.Parameters.Add(new SqlParameter("@NgaySX", SqlDbType.Date)).Value = hh.ngaySX.Date;
            cmd.Parameters.Add(new SqlParameter("@HanSD", SqlDbType.Date)).Value = hh.hanSD.Date;
            cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.VarChar)).Value = hh.ghiChu;
            cmd.Parameters.Add(new SqlParameter("@XuatXu", SqlDbType.VarChar)).Value = hh.xuatXu;
            cmd.Parameters.Add(new SqlParameter("@LoaiHang", SqlDbType.VarChar)).Value = hh.loaiHang;
            cmd.Parameters.Add(new SqlParameter("@KhoChua", SqlDbType.Char)).Value = hh.khoChua;
            cmd.ExecuteNonQuery();
        }
        public void HangHoa_Update(HangHoa hh)
        {
            SqlCommand cmd = new SqlCommand("UPDATE HangHoa SET SoLuong=@SoLuong,GiaGoc=@GiaGoc,VAT=@VAT,GhiChu=@GhiChu,KhoChua=@KhoChua WHERE MaHang=@MaHang", DataAccess.GetConnection());
            cmd.Parameters.Add(new SqlParameter("@SoLuong", SqlDbType.Int)).Value = hh.soLuong;
            cmd.Parameters.Add(new SqlParameter("@GiaGoc", SqlDbType.Int)).Value = hh.giaGoc;
            cmd.Parameters.Add(new SqlParameter("@VAT", SqlDbType.Int)).Value = hh.VAT;
            cmd.Parameters.Add(new SqlParameter("@GhiChu", SqlDbType.VarChar)).Value = hh.ghiChu;
            cmd.Parameters.Add(new SqlParameter("@KhoChua", SqlDbType.Char)).Value = hh.khoChua;
            cmd.Parameters.Add(new SqlParameter("@MaHang", SqlDbType.Char)).Value = hh.maHang;
            cmd.ExecuteNonQuery();
        }
        public void HangHoang_Delete(HangHoa hh)
        {
            SqlCommand cmd = new SqlCommand("DELETE FROM HangHoa WHERE MaHang=@MaHang", DataAccess.GetConnection());
            cmd.Parameters.Add(new SqlParameter("@MaHang", SqlDbType.Char)).Value = hh.maHang;
            cmd.ExecuteNonQuery();
        }
        public List<HangHoa> HangHoa_GetData()
        {
            List<HangHoa> listHH = new List<HangHoa>();
            SqlDataAdapter sda = new SqlDataAdapter("SELECT * FROM HangHoa", DataAccess.GetConnection());
            DataTable dt = new DataTable();
            sda.Fill(dt);
            HangHoa hh;
            for (int i = 0; i < dt.Rows.Count; i++)
            {
                hh = new HangHoa();
                hh.maHang = dt.Rows[i]["MaHang"].ToString();
                hh.tenHang = dt.Rows[i]["TenHang"].ToString();
                hh.donVi = dt.Rows[i]["DonVi"].ToString();
                hh.maNPP = dt.Rows[i]["MaNPP"].ToString();
                hh.soLuong = Convert.ToInt32(dt.Rows[i]["SoLuong"].ToString());
                hh.giaGoc = Convert.ToInt32(dt.Rows[i]["GiaGoc"].ToString());
                hh.soLuongDongGoi = Convert.ToInt32(dt.Rows[i]["SoLuongDongGoi"].ToString());
                if (dt.Rows[i]["GiaDongGoi"].ToString() != "")
                    hh.giaDongGoi = Convert.ToInt32(dt.Rows[i]["GiaDongGoi"].ToString());
                else
                    hh.giaDongGoi = 0;
                hh.VAT = Convert.ToInt32(dt.Rows[i]["VAT"].ToString());
                hh.tenNSX = dt.Rows[i]["TenNSX"].ToString();
                if (dt.Rows[i]["NgaySX"].ToString() != "")
                    hh.ngaySX = Convert.ToDateTime(dt.Rows[i]["NgaySX"].ToString());
                if (dt.Rows[i]["HanSD"].ToString() != "")
                    hh.hanSD = Convert.ToDateTime(dt.Rows[i]["HanSD"].ToString());
                hh.ghiChu = dt.Rows[i]["GhiChu"].ToString();
                hh.xuatXu = dt.Rows[i]["XuatXu"].ToString();
                hh.loaiHang = dt.Rows[i]["LoaiHang"].ToString();
                hh.khoChua = dt.Rows[i]["KhoChua"].ToString();
                listHH.Add(hh);
            }
            return listHH;
        }

        public bool HangHoa_CheckId(string a)
        {
            SqlCommand cmd = new SqlCommand("SELECT MaHang FROM HangHoa", DataAccess.GetConnection());
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (a == dr["MaHang"].ToString()) return true;
            }
            return false;
        }
        public SqlDataReader HangHoa_Get(string a,string b)
        {
            SqlCommand cmd = new SqlCommand(a, DataAccess.GetConnection());
            if(a.Contains("WHERE LoaiHang=@LoaiHang"))
            {
                cmd.Parameters.Add(new SqlParameter("@LoaiHang", SqlDbType.VarChar)).Value = b;
            }
            if(a.Contains("WHERE MaHang=@MaHang"))
            {
                cmd.Parameters.Add(new SqlParameter("@MaHang", SqlDbType.Char)).Value = b;
            }
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            return dr;
        }
        public List<HangHoa> HangHoa_SearchName(string a)
        {
            List<HangHoa> listHH = new List<HangHoa>();
            SqlCommand cmd = new SqlCommand("SELECT * FROM HangHoa", DataAccess.GetConnection());
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (a == dr["TenHang"].ToString())
                {
                    HangHoa hh = new HangHoa();
                    hh.maHang = dr["MaHang"].ToString();
                    hh.tenHang = dr["TenHang"].ToString();
                    hh.donVi = dr["DonVi"].ToString();
                    hh.maNPP = dr["MaNPP"].ToString();
                    hh.soLuong = Convert.ToInt32(dr["SoLuong"].ToString());
                    hh.VAT = Convert.ToInt32(dr["VAT"].ToString());
                    hh.tenNSX = dr["TenNSX"].ToString();
                    hh.ngaySX = Convert.ToDateTime(dr["NgaySX"]);
                    hh.hanSD = Convert.ToDateTime(dr["HanSD"]);
                    hh.ghiChu = dr["GhiChu"].ToString();
                    hh.xuatXu = dr["XuatXu"].ToString();
                    hh.loaiHang = dr["LoaiHang"].ToString();
                    hh.khoChua = dr["KhoChua"].ToString();
                    listHH.Add(hh);
                }
            }
            return listHH;
        }
        public List<HangHoa> HangHoa_CheckExp()
        {
            List<HangHoa> listHH = new List<HangHoa>();
            SqlCommand cmd = new SqlCommand("SELECT * FROM HangHoa", GetConnection());
            SqlDataReader dr;
            dr = cmd.ExecuteReader();
            while (dr.Read())
            {
                if (DateTime.Now > Convert.ToDateTime(dr["HanSD"]))
                {
                    HangHoa hh = new HangHoa();
                    hh.maHang = dr["MaHang"].ToString();
                    hh.tenHang = dr["TenHang"].ToString();
                    hh.donVi = dr["DonVi"].ToString();
                    hh.maNPP = dr["MaNPP"].ToString();
                    hh.soLuong = Convert.ToInt32(dr["SoLuong"].ToString());
                    hh.soLuongDongGoi = Convert.ToInt32(dr["SoLuongDongGoi"].ToString());
                    hh.giaDongGoi = Convert.ToInt32(dr["GiaDongGoi"].ToString());
                    hh.giaGoc = Convert.ToInt32(dr["GiaGoc"].ToString());
                    hh.VAT = Convert.ToInt32(dr["VAT"].ToString());
                    hh.tenNSX = dr["TenNSX"].ToString();
                    hh.ngaySX = Convert.ToDateTime(dr["NgaySX"]);
                    hh.hanSD = Convert.ToDateTime(dr["HanSD"]);
                    hh.ghiChu = dr["GhiChu"].ToString();
                    hh.xuatXu = dr["XuatXu"].ToString();
                    hh.loaiHang = dr["LoaiHang"].ToString();
                    hh.khoChua = dr["KhoChua"].ToString();
                    listHH.Add(hh);
                }
            }
            return listHH;
        }

        public DataTable SelectNameHangHoa(string maHang)
        {
            SqlCommand cmd = new SqlCommand("Select * from HangHoa where maHang=@maHang", DataAccess.GetConnection());
            cmd.Parameters.Add(new SqlParameter("@MaHang", SqlDbType.Char)).Value = maHang;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            return dt;
        }
        public HangHoa GetHangHoaObj(string maHang)
        {
            HangHoa hh = new HangHoa();
            SqlCommand cmd = new SqlCommand("Select * from HangHoa where maHang=@maHang", DataAccess.GetConnection());
            cmd.Parameters.Add(new SqlParameter("@Mahang", SqlDbType.Char)).Value = maHang;
            SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataTable dt = new DataTable();
            da.Fill(dt);
            hh.maHang = dt.Rows[0]["MaHang"].ToString();
            hh.tenHang = dt.Rows[0]["TenHang"].ToString();
            hh.donVi = dt.Rows[0]["DonVi"].ToString();
            hh.maNPP = dt.Rows[0]["MaNPP"].ToString();
            hh.soLuong = Convert.ToInt32(dt.Rows[0]["SoLuong"].ToString());
            hh.giaGoc = Convert.ToInt32(dt.Rows[0]["GiaGoc"].ToString());
            hh.soLuongDongGoi = Convert.ToInt32(dt.Rows[0]["SoLuongDongGoi"].ToString());
            hh.giaDongGoi = Convert.ToInt32(dt.Rows[0]["GiaDongGoi"].ToString());
            hh.VAT = Convert.ToInt32(dt.Rows[0]["VAT"].ToString());
            hh.tenNSX = dt.Rows[0]["TenNSX"].ToString();
             hh.ngaySX = Convert.ToDateTime(dt.Rows[0]["NgaySX"].ToString());
            hh.hanSD = Convert.ToDateTime(dt.Rows[0]["HanSD"].ToString());
            hh.ghiChu = dt.Rows[0]["GhiChu"].ToString();
            hh.xuatXu = dt.Rows[0]["XuatXu"].ToString();
            hh.loaiHang = dt.Rows[0]["LoaiHang"].ToString();
            hh.khoChua = dt.Rows[0]["KhoChua"].ToString();
            return hh;
        }
    }
}
